﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Data.Common;
using Data.Enum;

namespace DataAccess
{
    public class DbBase
    {
        private static string _dbstr = string.Empty;

        private static SqlConnection _conn;
        private static SqlCommand _commandSql;
        private static SqlCommand _commandPro;

        public DbBase()
        {
            try
            {
                //SetDevModeString();
                SetBetaModeString();
                if (_conn == null)
                {
                    _conn = new SqlConnection(_dbstr);
                }
                if (_conn.State != ConnectionState.Open)
                {
                    _conn.Open();
                }
            }
            catch (Exception)
            {
                if (_conn != null && _conn.State != ConnectionState.Closed)
                {
                    CloseConn();
                }
            }
        }

        private static void SetBetaModeString()
        {
            string dbaddress = ConfigurationManager.AppSettings["dbAddress"];
            string dbname = ConfigurationManager.AppSettings["dbName"];
            string username = ConfigurationManager.AppSettings["dbUserName"];
            string password = ConfigurationManager.AppSettings["dbPwd"];
            string dbstr = string.Format("server={0};uid={2};pwd={3};Trusted_Connection=no;database={1}", dbaddress,
                dbname, username, password);
            _dbstr = dbstr;
        }

        private void SetDevModeString()
        {
            _dbstr = System.Configuration.ConfigurationManager.ConnectionStrings["dbstr"].ConnectionString;
        }

        public ResultInfo<bool> TestConnection(string dbaddress, string dbname, string username, string password)
        {
            var result = new ResultInfo<bool> {ResultNumber = ResultEnum.Error};
            try
            {
                string dbstr = string.Format("server={0};uid={2};pwd={3};Trusted_Connection=no;database={1}", dbaddress,
                    dbname, username, password);
                try
                {
                    var connection = new SqlConnection(dbstr);
                    connection.Open();
                    connection.Close();
                    connection.Dispose();
                    result.Obj = true;
                    result.ResultNumber = ResultEnum.Success;
                    result.Describe = "连接正常";
                }
                catch (Exception e1)
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = e1.Message;
                }
            }
            catch (Exception)
            {
                result.Describe = "配置文件不完整，请联系管理员";
                result.ResultNumber = ResultEnum.Error;
            }
            return result;
        }

        protected SqlCommand GetSqlCommand()
        {
            try
            {
                if (_commandSql == null)
                {
                    _commandSql = _conn.CreateCommand();
                    _commandSql.CommandType = CommandType.Text;
                }
            }
            catch (Exception)
            {
                _commandSql = null;
            }
            return _commandSql;
        }

        protected SqlCommand GetProCommand()
        {
            try
            {
                _commandPro = null; //不为空会有参数累计，所以每次都去获取新的
                if (_commandPro == null)
                {
                    _commandPro = _conn.CreateCommand();
                    _commandPro.CommandType = CommandType.StoredProcedure;
                }
            }
            catch (Exception)
            {
                _commandPro = null;
            }
            return _commandPro;
        }

        protected int ExcuteSql(string sqlCommand)
        {
            int result = 0;
            if (_conn != null && _conn.State == ConnectionState.Open)
            {
                SqlTransaction trans = _conn.BeginTransaction();
                try
                {
                    SqlCommand command = _conn.CreateCommand();
                    command.Transaction = trans;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlCommand;
                    result = command.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    //throw;
                }
                //_conn.Close();
            }
            return result;
        }

        protected DataSet ExcuteSqlDataSet(string sqlCommand)
        {
            var result = new DataSet();
            if (_conn != null && _conn.State == ConnectionState.Open)
            {
                SqlTransaction trans = _conn.BeginTransaction();
                try
                {
                    SqlCommand command = _conn.CreateCommand();
                    command.Transaction = trans;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlCommand;
                    var da = new SqlDataAdapter(command);
                    da.Fill(result);
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    result = null;
                    //throw;
                }
                //_conn.Close();
            }
            return result;
        }

        public void CloseConn()
        {
            try
            {
                if (_commandPro != null)
                {
                    _commandPro = null;
                }
                if (_commandSql != null)
                {
                    _commandSql = null;
                }
                if (_conn != null && _conn.State != ConnectionState.Closed)
                {
                    _conn.Close();
                    _conn = null;
                }else
                {
                    _conn = null;
                    GC.Collect();
                }
            }
            catch (Exception e)
            {
            }
        }
    }
}